#!/usr/bin/python2.4
#
# Copyright 2011 Google Inc. All Rights Reserved.

import ftclient, csv
TABLE = "393082"
#TABLE = "391778"

def updateTable():
  password = open("password.txt").read().strip()
  authtoken = ftclient.GoogleClientLogin('qldroadclosures@gmail.com', password)

  ft = ftclient.FTClient(authtoken)

  out = ft.runGetQuery("select rowid, name, time from %s" % TABLE)

  input = csv.reader(out.split('\n'))

  existing = {}

  for line in input:
    if not line: continue
    rowid, name, date = line
    if rowid == 'rowid': continue
    existing[(name, date)] = rowid

  new = {}

  input2 = csv.reader(open('all_new.csv'))

  for line in input2:
    if not line: continue
    geo, title, date, description = line
    new[(title, date)] = (geo, title, date, description)

  to_remove = []
  to_add = []
  to_keep = []

  for entry in existing.keys():
    if entry in new:
      # keep
      to_keep.append((entry, existing[entry]))
    else:
      to_remove.append((entry, existing[entry]))

  for entry in new.keys():
    if entry in existing:
      # keep
      pass
    else:
      to_add.append((entry, new[entry]))

  print len(to_remove), len(to_add), len(to_keep)

  to_remove = set(x[1] for x in to_remove)
  for data in to_remove:
    query = "delete from %s where rowid = '%s'"%(TABLE, data)
    print query
    ft.runGetQuery(query)

  to_add = [x[1] for x in to_add]
  for data in to_add:
    desc = data[3]
    desc = desc.replace("'", "\\'")
    query = "insert into %s (geolocation,name,time,description,icon) VALUES ('%s', '%s', '%s', '%s', '%s')" % ( 
        TABLE, data[0], data[1], data[2], desc, "placemark_circle_highlight")
    print query
    ft.runGetQuery(query)

  # Once off to add a new icon.
  #for data in to_keep:
  #  query = "update %s SET icon = 'placemark_circle_highlight' WHERE rowid = '%s'" % (TABLE, data[1])
  #  print query 
  #  ft.runGetQuery(query)
  
if __name__ == "__main__":
  updateTable()
